Using SQL Expressions for Case Logic

If your database supports Case Logic, and your report needs to summarize an If-Then-Else formula calculation, replace the formula with an SQL Expression field. In such cases, SQL Expression fields enable Crystal Reports to perform the report's grouping on the server.

For instance, suppose that you're reporting off of an MS SQL Server 7 database, which supports Case Logic. You need to include an If-Then-Else calculation in your report, and you need to summarize that calculation for each group in the report. By performing the calculation with an SQL Expression field of the following form, you use your database's ability to process Case Logic:

CASE DatabaseTable."DatabaseField"
WHEN 'SpecifiedValue' THEN Calculation1 
ELSE Calculation2
END

If a record's DatabaseField value is equal to SpecifiedValue, then Calculation1 is performed; for all other records, Calculation2 is performed. By incorporating the SQL Expression field, you take advantage of your database server's ability to process Case Logic. Your report's grouping consequently takes place on the server, even when you summarize the SQL Expression field elsewhere in the report.

Note:    The SQL syntax in this example is specific to MS SQL Server 7. You may need to refer to your database documentation or consult with your Administrator in order to determine the syntax appropriate to your database.



Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com